const mysql = require('mysql2/promise');

async function verifyShopStatusUpdate () {
  const connection = await mysql.createConnection({
    host: '43.143.253.188',
    port: 3306,
    user: 'nextjs',
    password: 'yEAiRE2jcxTyRBy5',
    database: 'nextjs',
    timezone: '+08:00'
  });

  try {
    console.log('🔍 验证商店状态更新结果...\n');

    // 验证宝物商店物品
    console.log('🏪 宝物商店物品验证:');
    const [treasureItems] = await connection.execute(`
      SELECT name, silver_price, pic, is_treasure_shop, is_exp_shop
      FROM basic_items
      WHERE is_treasure_shop = 1
      ORDER BY silver_price DESC
    `);

    console.log(`  共 ${treasureItems.length} 个物品:`);
    treasureItems.forEach((item, index) => {
      const shopStatus = item.is_treasure_shop && item.is_exp_shop ? '宝物+经验' :
        item.is_treasure_shop ? '宝物商店' :
          item.is_exp_shop ? '经验商店' : '未上架';
      console.log(`  ${index + 1}. ${item.name} - ${item.silver_price}两 - ${item.pic} (${shopStatus})`);
    });

    // 验证经验商店物品
    console.log('\n📚 经验商店物品验证:');
    const [expItems] = await connection.execute(`
      SELECT name, silver_price, pic, is_treasure_shop, is_exp_shop
      FROM basic_items
      WHERE is_exp_shop = 1
      ORDER BY silver_price DESC
    `);

    console.log(`  共 ${expItems.length} 个物品:`);
    expItems.forEach((item, index) => {
      const shopStatus = item.is_treasure_shop && item.is_exp_shop ? '宝物+经验' :
        item.is_treasure_shop ? '宝物商店' :
          item.is_exp_shop ? '经验商店' : '未上架';
      console.log(`  ${index + 1}. ${item.name} - ${item.silver_price}两 - ${item.pic} (${shopStatus})`);
    });

    // 统计信息
    const [stats] = await connection.execute(`
      SELECT 
        COUNT(*) as total_items,
        COUNT(CASE WHEN is_treasure_shop = 1 AND is_exp_shop = 0 THEN 1 END) as treasure_only,
        COUNT(CASE WHEN is_treasure_shop = 0 AND is_exp_shop = 1 THEN 1 END) as exp_only,
        COUNT(CASE WHEN is_treasure_shop = 1 AND is_exp_shop = 1 THEN 1 END) as both_shops,
        COUNT(CASE WHEN is_treasure_shop = 0 AND is_exp_shop = 0 THEN 1 END) as no_shop
      FROM basic_items
    `);

    console.log('\n📊 最终统计:');
    console.log(`  总物品数: ${stats[0].total_items}`);
    console.log(`  仅宝物商店: ${stats[0].treasure_only} 个`);
    console.log(`  仅经验商店: ${stats[0].exp_only} 个`);
    console.log(`  两个商店都有: ${stats[0].both_shops} 个`);
    console.log(`  未上架: ${stats[0].no_shop} 个`);

    // 验证特定物品
    console.log('\n🎯 特定物品验证:');
    const specificItems = [
      '将军令', '大将军令', '护身符', '青铜钥匙', '白银钥匙', '黄金钥匙',
      '体力丹', '易名神符', '原木钥匙', '精钢钥匙', '珍奇钥匙', '兵符',
      '手里剑', '签到令牌', '壕客令牌', '超级壕客令牌',
      '副将经验书', '初级副将心法', '高级副将心法', '特级副将心法',
      '密宗副将心法', '一级技能书', '二级技能书', '三级技能书',
      '四级技能书', '五级技能书'
    ];

    for (const itemName of specificItems) {
      const [item] = await connection.execute(`
        SELECT name, silver_price, pic, is_treasure_shop, is_exp_shop
        FROM basic_items
        WHERE name = ?
      `, [itemName]);

      if (item.length > 0) {
        const itemData = item[0];
        const shopStatus = itemData.is_treasure_shop && itemData.is_exp_shop ? '宝物+经验' :
          itemData.is_treasure_shop ? '宝物商店' :
            itemData.is_exp_shop ? '经验商店' : '未上架';
        console.log(`  ✅ ${itemData.name} - ${itemData.silver_price}两 - ${itemData.pic} (${shopStatus})`);
      } else {
        console.log(`  ❌ 未找到物品: ${itemName}`);
      }
    }

    // 检查是否有重复上架的物品
    const [duplicates] = await connection.execute(`
      SELECT name, is_treasure_shop, is_exp_shop
      FROM basic_items
      WHERE is_treasure_shop = 1 AND is_exp_shop = 1
    `);

    if (duplicates.length > 0) {
      console.log('\n⚠️ 同时上架两个商店的物品:');
      duplicates.forEach(item => {
        console.log(`  ${item.name}`);
      });
    } else {
      console.log('\n✅ 无重复上架物品');
    }

    console.log('\n🎉 商店状态验证完成！');

  } catch (error) {
    console.error('❌ 验证失败:', error.message);
    if (error.code) {
      console.error('错误代码:', error.code);
    }
  } finally {
    await connection.end();
    console.log('\n🔌 数据库连接已关闭');
  }
}

// 运行脚本
if (require.main === module) {
  verifyShopStatusUpdate().catch(console.error);
}

module.exports = { verifyShopStatusUpdate };
